-- STANDARD SCRIPT
--


--------------------------------------------------------------------------------------
-- FUNCTION TO UPDATE BATCH INSURANCE TABLE WITH ZIP4
-- IN PARTICULAR SELECT ACCOUNTS AVAILABLE FOR SCORING


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_batch_insurance1_zip4_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
  v_count integer;
BEGIN



-- AUDIT OF ZIP4 AND GACA

SELECT INTO v_count count(*) FROM  batch_zip4_insurance1_t;

IF (v_count = 0) THEN

   RAISE EXCEPTION 'AUDIT ERROR IN batch_zip4_insurance1_t: TABLE IS EMPTY - QAS DATA MAY HAVE EXPIRED - RENEW DATA ';

END IF;


SELECT INTO v_count count(*) FROM (select 1 FROM ml_gaca_demographics_t LIMIT 1) as a;

IF (v_count = 0) THEN

   RAISE EXCEPTION 'AUDIT ERROR IN ml_gaca_demographics_t: TABLE IS EMPTY ';

END IF;


-- END OF AUDIT



-- UPDATE ZIP4 WITH FIELD IN batch_zip4_insurance1_t 
-- 
 
RAISE NOTICE 'START UPDATING zip4 IN batch_insurance1_t';

SELECT INTO v_batch_run_date our_run_date from batch_insurance1_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance1_t limit 1;
PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,4,'batch_insurance1_t ZIP4 APPEND BEGIN');



  UPDATE batch_insurance1_t
  SET
     zip5 = (CASE WHEN batch_insurance1_t.zip5 IS NOT NULL THEN batch_insurance1_t.zip5
                  ELSE                                          substring(ba.zipcode from 1 for 5)
                  END),
     zip4           =  substring(ba.zipcode from 7 for 4)
  FROM
     batch_zip4_insurance1_t ba
  WHERE
    (ba.seqnum = batch_insurance1_t.seqnum ) ;



GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH UPDATE OF zip4 IN batch_insurance1_t : %', v_status;



--  UPDATE batch_status_insurance1_t WITH THE NUMBER UPDATED

  UPDATE batch_status_insurance1_t
    SET
      zip4_updated        = v_status
    WHERE
      (batch_status_insurance1_t.last_run_date = v_batch_run_date and batch_status_insurance1_t.cust_id = v_cust_id);



PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,4,'batch_insurance1_t ZIP4 APPEND END');


  return v_status;
END;
$$ LANGUAGE plpgsql;
